import sqlite3

from cons import DATABASE_NAME

print(DATABASE_NAME)


def user_add(user_name, password):
    """添加用户"""
    conn = sqlite3.connect(DATABASE_NAME)
    try:
        res = conn.execute('SELECT * FROM USERS WHERE user_name = ?', (user_name,))
        res = res.fetchone()
        print("添加用户查询结果：", res)
        if not res:
            conn.execute("INSERT INTO USERS(user_name, password) VALUES (?,?)", (user_name, password))
            conn.commit()
        else:
            conn.close()
            return "该用户已存在"
    except Exception as e:
        conn.rollback()  # 数据回滚操作，撤销上一次commit提交
        return "添加失败" + str(e)
    else:
        return "用户添加成功"
    finally:
        conn.close()


def add_words(words_name, words_mean):
    """添加单词"""
    conn = sqlite3.connect(DATABASE_NAME)
    try:
        res = conn.execute('SELECT * FROM WORDS WHERE words_name = ?', (words_name,))
        # print("添加用户查询结果：", res.fetchone(),bool(res.fetchone()))
        res = res.fetchone()
        if not res:
            conn.execute("INSERT INTO WORDS(words_name,words_mean) VALUES (?,?)", (words_name, words_mean))
            conn.commit()
        else:
            conn.close()
            return res
    except Exception as e:
        return "添加失败" + str(e)
    else:
        return res
    finally:
        conn.close()


def user_query_all():
    """查询所有用户"""
    conn = sqlite3.connect(DATABASE_NAME)
    try:
        data = conn.execute("""SELECT * FROM USERS""")
    except Exception as e:
        print("查找失败", e)
    else:
        print("查找成功！")
        for d in data:
            print(d)
    finally:
        conn.close()


def words_query_all():
    """查询所有单词"""
    conn = sqlite3.connect(DATABASE_NAME)
    try:
        data = conn.execute("""SELECT * FROM WORDS""")
    except Exception as e:
        print("查找失败", e)
    else:
        print("查找成功！")
        for d in data:
            print(d)
    finally:
        conn.close()

def account_modify(id,account,real_password):
    conn = sqlite3.connect(DATABASE_NAME)
    try:
        conn.execute('UPDATE USERS SET user_name =?,password = ? WHERE user_id = ?', (account, real_password, id))
    except Exception as e:
        conn.rollback()
        return "修改失败" + str(e)
    else:
        conn.commit()
        user_query_all()
        return "用户账户密码修改成功"
    finally:
        conn.close()

def find_user(user_name, password):
    conn = sqlite3.connect(DATABASE_NAME)
    try:
        res = conn.execute('SELECT * FROM USERS WHERE user_name = ?', (user_name,))
        res = res.fetchone()
        print("用户查询结果：", res)
        if not res:
            print("该用户不存在")
            conn.close()
            return 0  # 该用户不存在
        else:
            if password == res[2]:
                conn.close()
                return res
            else:
                conn.close()
                return -1
            conn.commit()
    except:
        print("查询错误")
    finally:
        conn.close()


def add_userwords(user_id, words_id):
    """添加用户单词"""
    conn = sqlite3.connect(DATABASE_NAME)
    try:
        res = conn.execute('SELECT * FROM USER_WORDS WHERE user_id = ? AND words_id=?', (user_id,words_id))
        res = res.fetchone()
        print("add_userwords：",res)
        if not res:
            conn.execute('INSERT INTO USER_WORDS(user_id,words_id) VALUES (?,?)', (user_id, words_id))
        else:
            conn.close()
            return "单词已存在"
    except Exception as e:
        conn.rollback()
        return "添加失败" + str(e)
    else:
        conn.commit()
        return "用户单词添加成功"
    finally:
        conn.close()

def add_usernote(user_id,note):
    """添加用户笔记"""
    conn = sqlite3.connect(DATABASE_NAME)
    try:
        conn.execute('INSERT INTO USER_NOTE(user_id,note) VALUES (?,?)', (user_id,note))
    except Exception as e:
        conn.rollback()
        return "添加失败" + str(e)
    else:
        conn.commit()
        return "用户笔记添加成功"
    finally:
        conn.close()

def del_usernote(user_id,note_id):
    conn = sqlite3.connect(DATABASE_NAME)
    try:
        conn.execute('DELETE FROM USER_NOTE WHERE user_id = ? and note_id = ?', (user_id,note_id))
    except Exception as e:
        conn.rollback()
        return "删除失败" + str(e)
    else:
        conn.commit()
        return "用户笔记删除成功"
    finally:
        conn.close()

def del_userword(user_id,words_name):
    conn = sqlite3.connect(DATABASE_NAME)
    try:
        conn.execute('DELETE FROM USER_WORDS WHERE user_id = ? and words_id = (SELECT words_id FROM WORDS WHERE words_name=?)', (user_id,words_name))
    except Exception as e:
        conn.rollback()
        return "删除失败" + str(e)
    else:
        conn.commit()
        return "用户单词删除成功"
    finally:
        conn.close()

def update_usernote(user_id,note_id,note):
    conn = sqlite3.connect(DATABASE_NAME)
    try:
        conn.execute('UPDATE USER_NOTE SET note=? WHERE user_id = ? and note_id = ?', (note,user_id,note_id))
    except Exception as e:
        conn.rollback()
        return "修改失败" + str(e)
    else:
        conn.commit()
        return "用户笔记修改成功"
    finally:
        conn.close()

def find_note(user_id):
    conn = sqlite3.connect(DATABASE_NAME)
    try:
        res = conn.execute('SELECT * FROM USER_NOTE WHERE user_id = ?', (user_id,))
        res = res.fetchall()
        print("用户查询结果：", res)
        return res
    except:
        return "查询错误"
    finally:
        conn.close()


def find_userword(user_id):
    """查询用户下的所有单词"""
    conn = sqlite3.connect(DATABASE_NAME)
    try:
        # rre = conn.execute("SELECT words_id FROM USER_WORDS WHERE user_id = ?",(user_id,))
        # rre = conn.execute("SELECT a.words_id,a.words_name,a.words_mean,b.words_status,b.user_id FROM WORDS AS a JOIN  USER_WORDS AS b JOIN USERS AS c WHERE a.words_id=b.words_id AND b.user_id=c.user_id")
        # print(rre.fetchall())
        res = conn.execute('SELECT words_name,words_mean FROM WORDS WHERE words_id in (SELECT words_id FROM USER_WORDS WHERE user_id = ?)',(user_id,))
        # res = conn.execute('SELECT a.words_id,a.words_name,a.words_mean,b.words_status,b.user_id FROM WORDS AS a INNER JOIN  USER_WORDS AS b WHERE a.words_id IN (SELECT words_id FROM USER_WORDS WHERE user_id = ?)',
        #                    (user_id,))
        res = res.fetchall()
        print("用户查询结果：", res)
        return res
    except:
        print("查询错误")
    finally:
        conn.close()



if __name__ == '__main__':
    # print(user_add('aa','hihihi'))
    # print(user_add('bb','hahaha'))
    # print(user_add('ccc','lalala'))
    # print(add_words('hello','你好'))
    # print(add_words('word','世界'))
    # print(add_words('hi','嗨'))
    # print(add_userwords(1,1))
    # print(add_userwords(1,2))
    # print(add_userwords(1,3))
    # print(add_userwords(2,1))
    # print(add_userwords(2,3))
    # print(add_userwords(3,2))
    # user_query_all()
    words_query_all()
    for i in find_userword(2):
        print(i)